
******************marginal tax rate according to profit chargable

use "${hmrc_dir}\Data\CT600\ct600_fame.dta", clear
merge m:1 year using "${hmrc_dir}\Data\CBT\marginal tax rate.dta"
drop if _merge==2
drop _merge

gen marginal_tax=.
replace marginal_tax=x10000 if profits_chargeable_r<=10000 & profits_chargeable_r>0
count if profits_chargeable_r==10000
replace marginal_tax=x150000 if profits_chargeable_r>10000
replace marginal_tax=x300000 if profits_chargeable_r>50000
replace marginal_tax=x1500000 if profits_chargeable_r>300000
replace marginal_tax=x if profits_chargeable_r>1500000
replace marginal_tax=0 if profits_chargeable_r==0
count if marginal_tax==.

*Effective tax rate
gen corp_tax=profits_chargeable_r*x10000 if profits_chargeable_r<10000|profits_chargeable_r==10000
replace corp_tax=(profits_chargeable_r-10000)*x150000+10000*x10000 if profits_chargeable_r>10000
replace corp_tax=(profits_chargeable_r-50000)*x300000+10000*x10000+40000*x150000 if profits_chargeable_r>50000
replace corp_tax=(profits_chargeable_r-300000)*x1500000+10000*x10000+40000*x150000+250000*x300000 if profits_chargeable_r>300000
replace corp_tax=(profits_chargeable_r-1500000)*x+10000*x10000+40000*x150000+250000*x300000+1200000*x1500000 if profits_chargeable_r>1500000
replace corp_tax=corp_tax/100

gen etr_a=corp_tax/trading_profit_r
gen etr_m=corp_tax/profits_chargeable_r
sum etr_a etr_m, d

bysort year: egen average_etr1=mean(etr_a) if etr_a!=.

sum etr_a, d
bysort year: egen average_etr_nout=mean(etr_a) if etr_a!=. & etr_a!=.<=r(p99)

bysort year: egen average_corp_tax=mean(corp_tax)
bysort year: egen average_trading_profit=mean(trading_profit_r)
gen average_etr2=average_corp_tax/average_trading_profit

rename etr_a etr
drop etr_m 
save "${hmrc_dir}\Data\CT600\ct600_fame_ETR.dta", replace

*************summary statistics
*tax paid in each year compared
use "${hmrc_dir}\Data\CT600\ct600_fame_ETR.dta", clear
summarize corp_tax etr
tab year, summarize(corp_tax) mean standard obs
/*ble year, c (mean corp_tax sd corp_tax n corp_tax) format(%9.2f) center row
tabstat corp_tax etr, by(year) stat(mean sd n) col(stat) long*/
bysort year: egen mean_tax_chargeable=mean(tax_chargeable)
twoway (connected mean_tax_chargeable year)  (connected average_corp_tax year), xlabel(#11, labels) legend(on)
graph save Graph "${hmrc_dir}\Notes\Data_description\corp_tax_yearaverage.gph",replace

gen diff_tax=corp_tax-tax_chargeable
sum diff_tax, d
gen ddiff_tax=diff_tax/tax_chargeable
sum ddiff_tax, d


tab year, summarize(etr) mean standard obs
sum etr if trading_profit_r>profits_chargeable
gen etr_taxcharg=tax_chargeable/trading_profit
sum etr_taxcharg
sum etr_taxcharg if trading_profit_r>profits_chargeable
sum etr if trading_profit_r>profits_chargeable&year>2000

replace overseas_income=0 if overseas_income==.
replace schedule_a_income=0 if schedule_a_income==.
replace schedule_d_case_vi_r=0 if schedule_d_case_vi_r==.
replace nt_loan_profit=0 if nt_loan_profit==.
replace schedule_d_case_iii=0 if schedule_d_case_iii==.
replace taxed_income=0 if taxed_income==.
gen sum_trading_profit=trading_profit_r+overseas_income+schedule_a_income+schedule_d_case_vi_r+nt_loan_profit+ schedule_d_case_iii+taxed_income

gen etr_sum=corp_tax/sum_trading_profit
gen etr_sum_taxchargeable=tax_chargeable/sum_trading_profit
/*aph bar (mean) corp_tax, over(type)
graph bar (mean) etr1, over(type)*/
histogram turnover_ct600 if turnover_ct600<150000, bin(10) fcolor(ltblue) addplot((histogram turnover_ct600 if turnover_ct600<150000& sum_trading_profits<profits_chargeable))


*number of loss making firms: defined as trading_losses>0 
gen l=1 if trading_losses_case_i_r>0&trading_losses_case_i_r!=.
tab l
bysort year identifier: gen n=_n
tab n
bysort year: egen number_loss=sum(l)
bysort year: egen number_companyyear=sum(n) if n==1
gen share_lossmaking=number_loss/number_companyyear
preserve
keep year share_lossmaking number_loss
duplicates drop
tabstat share_lossmaking number_loss, by(year)
twoway (connected share_lossmaking year), ytitle(# of lossmaking firms) xtitle(year)
graph save Graph "${hmrc_graphdir}\Data_description_ct600\share_loss_making.gph",replace
twoway (connected number_loss year), ytitle(# of lossmaking firms) xtitle(year)
graph save Graph "${hmrc_graphdir}\Data_description_ct600\no_loss_making.gph",replace
restore
*duration of loss making firms in each group: defined as trading_losses>0 
replace l=0 if l==.
bysort identifier: egen dur_loss=sum(l)
preserve
keep if dur_loss>0
keep identifier dur_loss
duplicates drop
sum dur_loss, d
histogram dur_loss, discrete width(1) percent ylabel(#10) xlabel(#12)
graph save Graph "${hmrc_graphdir}\Data_description_ct600\dur_loss_making.gph",replace
restore
*number of loss making firms: defined as profit_chargeable=0 
gen l2=1 if profits_chargeable==0
tab l2
bysort year identifier: gen n2=_n
tab n2
bysort year: egen number_loss2=sum(l2)
bysort year: egen number_companyyear2=sum(n2) if n==1
gen share_lossmaking2=number_loss2/number_companyyear2
preserve
keep year share_lossmaking2 number_loss2
duplicates drop
tabstat share_lossmaking2 number_loss2, by(year)
twoway (connected share_lossmaking2 year), ytitle(share of lossmaking firms2) xtitle(year)
graph save Graph "${hmrc_graphdir}\Data_description_ct600\share_loss_making2.gph",replace
twoway (connected number_loss2 year), ytitle(# of lossmaking firms2) xtitle(year)
graph save Graph "${hmrc_graphdir}\Data_description_ct600\no_loss_making2.gph",replace
restore
*duration of loss making firms in each group: defined as trading_losses>0 
replace l2=0 if l==.
bysort identifier: egen dur_loss2=sum(l2)
preserve
keep if dur_loss2>0
keep identifier dur_loss2
duplicates drop
sum dur_loss2, d
histogram dur_loss2
graph save Graph "${hmrc_graphdir}\Data_description_ct600\dur_loss_making2.gph",replace
restore


***************reduce the size of the data by dropping variables**********

use "${hmrc_dir}\Data\CT600\ct600_fame_ETR.dta", clear
keep turnover_ct600 group_relief taxed_income group_accounting ct_chargeable_r ///
profits_chargeable_r trading_losses_case_i_r trading_profit_r identifier year ///
depreciation fixed_assets gross_profit interest_paid long_term_debt no_employees ///
total_assets marginal_tax corp_tax m_and_p_exp* trading_losses_brought_forward_r ///
wages_and_salaries sic_primary incorp_date 

compress

save "${hmrc_datadir}\CT600\ct600_fame_short", replace

